﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using Vra.DataAccess.Entities;
using System.Windows.Forms;
using System.Data.Linq;

namespace Vra.DataAccess
{
    public class ArtistDao : IArtistDao
    {

        /// <summary>
        /// Возвращает строку подключения к базе
        /// </summary>
        /// <returns></returns>
        private static string GetConnectionString()
        {
            return ConfigurationManager.ConnectionStrings["vradb"].ConnectionString;
        }

        /// <summary>
        /// Возвращает объект подключения к базе
        /// </summary>
        /// <returns></returns>
        private static SqlConnection GetConnection()
        {
            return new SqlConnection(GetConnectionString());
        }

        private static Artist LoadArtist(SqlDataReader reader)
        {
            //Создаём пустой объект
            Artist artist = new Artist();
            //Заполняем поля объекта в соответствии с названиями полей результирующего
            // набора данных
            artist.ArtistId = reader.GetInt32(reader.GetOrdinal("ArtistID"));
            object birth = reader["BirthDate"];
            if ( birth!=DBNull.Value)
            artist.BirthYear = Convert.ToInt32(birth);
            //Помните, что у нас поле DeceasedDate может иметь значение NULL?
            //Следующие 3 строки корректно обрабатывают этот случай
            object decease = reader["DeceasedDate"];
            if (decease != DBNull.Value)
                artist.DeceaseYear = Convert.ToInt32(decease);
            artist.Name = reader.GetString(reader.GetOrdinal("ArtistName"));
            artist.Nationality = reader.GetString(reader.GetOrdinal("Nationality"));
            artist.TimeStamp = reader["TimeStamp"] as byte[];
            return artist;
        }



        public Artist Get(int id)
        {
            //Получаем объект подключения к базе
	        using (var conn = GetConnection())
	        {
		        //Открываем соединение
		        conn.Open();
		        //Создаем sql команду
		            using (var cmd = conn.CreateCommand())
		            {
			        //Задаём текст команды
			        cmd.CommandText = "SELECT ArtistID, ArtistName, BirthDate, DeceasedDate, Nationality, TimeStamp FROM ARTIST WHERE ArtistID = @id";
			        //Добавляем значение параметра
			        cmd.Parameters.AddWithValue("@id", id);
			        //Открываем SqlDataReader для чтения полученных в результате
                    // выполнения запроса данных
			            using (var dataReader = cmd.ExecuteReader())
			            {
				        return !dataReader.Read() ? null : LoadArtist(dataReader);
			            }
		             }
	         }           

          }

        public IList<Artist> GetAll()
        {
            IList<Artist> artists = new List<Artist>();
	        using (var conn = GetConnection())
	        {
		        conn.Open();
		       using (var cmd = conn.CreateCommand())
		       {
			    cmd.CommandText = "SELECT ArtistID, ArtistName, Birthdate, DeceasedDate, Nationality, TimeStamp FROM ARTIST";
			    using (var dataReader = cmd.ExecuteReader())
			    
                {
				    while (dataReader.Read())
				    {
					    artists.Add(LoadArtist(dataReader));
				    }
			    }
		       }
	      }
	        
            return artists;
         }

        public void Add(Artist artist)
        {
          

            using (var conn = GetConnection())
            {
                conn.Open();
              //  SqlCommand cmd = new SqlCommand("AddArtist", conn);
              //  cmd.CommandType = CommandType.StoredProcedure;
              //  cmd.Parameters.Add(new SqlParameter("@ArtistName", artist.Name));
              //  cmd.Parameters.Add(new SqlParameter("@Birthdate", artist.BirthYear));
              //  cmd.Parameters.Add(new SqlParameter("@DeceasedDate", artist.DeceaseYear));
              //  cmd.Parameters.Add(new SqlParameter("@Nationality", artist.Nationality));
              //  SqlParameter outputParam = new SqlParameter("@TimeStamp", SqlDbType.Timestamp);
              //  outputParam.Direction = ParameterDirection.Output;
              //  cmd.Parameters.Add(outputParam);
              //  try
              //  {
              //    cmd.ExecuteNonQuery();
              //  }
              //  catch (Exception ex)
              //  {
              //    MessageBox.Show("Исключение базы данных: " + ex.ToString(), "WARNING!");
              //  } 
              //artist.TimeStamp = cmd.Parameters["@TimeStamp"].Value as byte[];


              DataClasses1DataContext dc = new DataClasses1DataContext(conn);
              Binary timeStamp = new Binary(null);
              dc.AddArtist(artist.Name, artist.BirthYear, artist.DeceaseYear, artist.Nationality, ref timeStamp);
              artist.TimeStamp = timeStamp.ToArray();
              
              
              
              
              
              //using (var cmd = conn.CreateCommand())
                //{
                //    cmd.CommandText =
                //             "INSERT INTO ARTIST (Name,BirthYear,DeceaseYear,Nationality) VALUES (@Name,@BirthYear,@DeceaseYear,@Nationality)";
                //    cmd.Parameters.AddWithValue("@Name", artist.Name);
                //    cmd.Parameters.AddWithValue("@BirthYear", artist.BirthYear);
                //    cmd.Parameters.AddWithValue("@Nationality", artist.Nationality);
                //    object decease = artist.DeceaseYear.HasValue ?
                //                             (object)artist.DeceaseYear.Value : DBNull.Value;
                //    cmd.Parameters.AddWithValue("@DeceaseYear", decease);
                //    cmd.ExecuteNonQuery();
                //}
            }

        }

        public void Update(Artist artist)
        {
            using (var conn = GetConnection())
            {
              conn.Open();

              SqlCommand cmd = new SqlCommand("UpdateArtist", conn);
              cmd.CommandType = CommandType.StoredProcedure;
              cmd.Parameters.Add(new SqlParameter("@ArtistID", artist.ArtistId));
              cmd.Parameters.Add(new SqlParameter("@ArtistName", artist.Name));
              cmd.Parameters.Add(new SqlParameter("@Birthdate", artist.BirthYear));
              cmd.Parameters.Add(new SqlParameter("@DeceasedDate", artist.DeceaseYear));
              cmd.Parameters.Add(new SqlParameter("@Nationality", artist.Nationality));
              cmd.Parameters.Add(new SqlParameter("@TimeStamp", artist.TimeStamp));
              SqlParameter outputParam = new SqlParameter("@newTimeStamp", SqlDbType.Timestamp);
              outputParam.Direction = ParameterDirection.Output;
              cmd.Parameters.Add(outputParam);
              try
              {
                cmd.ExecuteNonQuery();
              }
              catch (Exception ex)
              {
                MessageBox.Show("Исключение базы данных: " + ex.ToString(), "WARNING!");
              } 
              artist.TimeStamp = cmd.Parameters["@newTimeStamp"].Value as byte[];

                //using (var cmd = conn.CreateCommand())
                //{
                //    cmd.CommandText = "UPDATE ARTIST SET Name = @Name, BirthYear = @BirthYear, DeceaseYear = @DeceaseYear, Nationality = @Nationality WHERE ArtistID = @ID";
                //    cmd.Parameters.AddWithValue("@Name", artist.Name);
                //    cmd.Parameters.AddWithValue("@BirthYear", artist.BirthYear);
                //    cmd.Parameters.AddWithValue("@ID", artist.ArtistId);
                //    cmd.Parameters.AddWithValue("@Nationality", artist.Nationality);
                //    object decease = artist.DeceaseYear.HasValue ?
                //                             (object)artist.DeceaseYear.Value : DBNull.Value;
                //    cmd.Parameters.AddWithValue("@DeceaseYear", decease);
                //    cmd.ExecuteNonQuery();
                //}
            }

        }

        public void Delete(int id)
        {
            using (var conn = GetConnection())
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("DeleteArtist", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@ArtistID", id));
                try
                {
                  cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                  MessageBox.Show("Исключение базы данных: " + ex.ToString(), "WARNING!");
                }

                //using (var cmd = conn.CreateCommand())
                //{
                //    cmd.CommandText = "DELETE FROM ARTIST WHERE ArtistID = @ID";
                //    cmd.Parameters.AddWithValue("@ID", id);
                //    try
                //    {
                //        cmd.ExecuteNonQuery();
                //    }
                //    catch (Exception ex)
                //    {
                //        MessageBox.Show("Исключение базы данных: " + ex.ToString(), "WARNING!");
                //    }
                //}
            }

        }
    }
}
